package com.jzwl.xydk.manager.school.schoollabel.dao;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.jzwl.common.id.Sequence;
import com.jzwl.common.page.PageObject;
import com.jzwl.system.base.dao.BaseDAO;

@Repository("schoolInfoDao")
public class SchoolInfoDao {
	@Autowired
	private BaseDAO baseDAO;//dao基类，操作数据库
	/**
	 * 查询学校列表
	 * @param paramsMap
	 * @return
	 */
	public PageObject querySchoolList(Map<String, Object> map) {
		
		String sql = " SELECT si.*,IFNULL(slb.schoolId,0)as 'lebel' FROM  tg_school_info si"
		
				+" LEFT JOIN `xiaoka-xydk`.schoollabel slb ON si.id=slb.schoolId WHERE si.isDelete = 0  AND si.id!=0 ";
		
		if(null !=map.get("cityId") && StringUtils.isNotEmpty(map.get("cityId").toString())){
				sql=sql+ " and si.cityId  = '" + map.get("cityId") +"'";
	  	}
		if(null !=map.get("schoolName") && StringUtils.isNotEmpty(map.get("schoolName").toString())){
			sql=sql+ " and si.schoolName  like '%" + map.get("schoolName") +"%'";
  	}
		sql = sql + " order by si.id desc";
		PageObject po = baseDAO.queryForMPageList(sql, new Object[]{},map);
		return po;
	}
	
	
	public List cityList() {
		String sql = " select t.id as cityId,t.cityName as cityName from tg_city t where 1=1 and t.isDelete = 0 ";
		return baseDAO.queryForList(sql);
	}
	
	public Map getSchoolInfo(String schoolId) {
		String sql = " select * from tg_school_info where id = "+schoolId +"";
		return baseDAO.queryForMap(sql);
	}
	
	
	public List<Map<String, Object>> toSchoolLabel(Map<String, Object> paramsMap) {
		try {
			String sql = "SELECT dt.id dataId,dt.dic_id dicId,dt.dic_name,dt.dic_value,dt.isDelete "
					+ " FROM v2_dic_data dt INNER JOIN v2_dic_type tp ON dt.dic_id=tp.dic_id "
					+ " WHERE tp.dic_code='SchoolLabel' AND dt.isDelete=0 AND tp.isDelete=0"; 
			return baseDAO.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public List<Map<String, Object>> querySkilDicdata(Map<String, Object> paramsMap) {
		try {
			String sql = "SELECT dcda.id as dicId FROM `xiaoka-xydk`.schoolLabel sl INNER JOIN `xiaoka`.v2_dic_data dcda ON dcda.id=sl.dicDataId WHERE sl.schoolId='"+paramsMap.get("schoolId")+"'";
			return baseDAO.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	
	public boolean updateLabel(Map<String, Object> map) {
		Date date = new Date();
		map.put("createTime", date);
		String yxId=(String) map.get("yxId");
		String yxIdArr[] = yxId.split(",");
		
			String sql = "INSERT INTO `xiaoka-xydk`.schoolLabel(dicDataId,schoolId,createTime) "
					+ "VALUES (:dicId,:schoolId,:createTime)";
			
			//先执行删除再添加
			String delSql ="DELETE FROM `xiaoka-xydk`.schoolLabel  WHERE schoolId IN('"+map.get("schoolId")+"')";
			baseDAO.executeNamedCommand(delSql, map);
			
					
			for(int i=0;i<yxIdArr.length;i++){
				if(yxIdArr[i]!=""&&yxIdArr[i]!=null){
					String dicId = yxIdArr[i];
					map.put("dicId", dicId);
					map.put("id",  Sequence.nextId());
					map.put("createDate",new Date());
					baseDAO.executeNamedCommand(sql, map);
				}
			}
			
			return true;
	}
	
	
	
	public List<Map<String, Object>> queryExit(Map<String, Object> paramsMap) {
		try {
			String sql = "SELECT id FROM `xiaoka-xydk`.dicdata_skillinfo "
			 +" WHERE skilinfoId='"+paramsMap.get("id")+"' ";
			return baseDAO.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

}













